/**
 * Company : Thai Informatic System Co., Ltd.
 * Created By : Chatree Siangklom
 * Created On : Apr 3, 2013  12:00:03 PM 
 * e-Mail : dr_chatree@hotmail.com
 * Mobile Phone : (+66) 087-921-5115
 * Package Name : promis.api.excel.write
 * File Name : ExcelToDbTableServices.java
 * TODO
 */
package promis.api.excel.write;

import java.io.File;
import java.sql.Connection;
import java.sql.Statement;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import promis.api.oracle.TableServices;
import promis.db.connection.dbHelper;
import promis.utility.CommonService;
import promis.utility.Utilitys;

/**
 * @author chatree
 *
 */
public class ExcelToDbTableServices {	
	
	private String inputFile = "";
	private String owner = "SYNCHRO";
	public ExcelToDbTableServices(String inputFilePath){
		this.inputFile = inputFilePath;
	}
	
	public final String [] PermananceTable = {"A_GEN_CODE",
			"A_GEN_DESC",
			"A_GEN_TYPE",
			"FI_APDEDUCTION_OUTB",
			"FI_APINVOICE_OUTB",
			"FI_APINVOICERENT_OUTB",
			"FI_ARCNRENT_OUTB",
			"FI_ARINVOICERECE_OUTB",
			"FI_ARINVRENT_OUTB",
			"FI_ARRECEIPTRE_OUTB",
			"FI_LIV_OUTGOINGPAYMENT_INB",
			"FI_LIV_OUTGOINGPAYMENT_INB_T",
			"FI_RE_RECEIPT_INB",
			"FI_VC_GLPOSTING_OUTB",
			"LOGS",
			"LOGS_INBOUND",
			"LOGS_INBOUND_201210",
			"LOGS_INBOUND_201211",
			"LOGS_INBOUND_201212",
			"LOGS_INBOUND_201301",
			"LOGS_INBOUND_201302",
			"LOGS_INBOUND_201303",
			"LOGS_INBOUND_201304",
			"LOGS_INBOUND_201305",
			"LOGS_INBOUND_201306",
			"LOGS_INBOUND_201307",
			"LOGS_INBOUND_201308",
			"LOGS_INBOUND_201309",
			"LOGS_INBOUND_201310",
			"LOGS_INBOUND_201311",
			"LOGS_INBOUND_201312",
			"LOGS_INBOUND_201401",
			"LOGS_INBOUND_201402",
			"LGS_INBOUND_201403",
			"LOGS_INBOUND_201404",
			"LOGS_INBOUND_201405",
			"LOGS_INBOUND_201406",
			"LOGS_INBOUND_201407",
			"LOGS_INBOUND_201408",
			"LOGS_INBOUND_201409",
			"LOGS_INBOUND_201410",
			"LOGS_INBOUND_201411",
			"LOGS_INBOUND_201412",
			"LOGS_INBOUND_201501",
			"LOGS_INBOUND_201502",
			"LOGS_INBOUND_201503",
			"LOGS_INBOUND_201504",
			"LOGS_INBOUND_201505",
			"LOGS_INBOUND_201506",
			"LOGS_INBOUND_201507",
			"LOGS_INBOUND_201508",
			"LOGS_INBOUND_201509",
			"LOGS_INBOUND_201510",
			"LOGS_INBOUND_201511",
			"LOGS_INBOUND_201512",
			"LOGS_OUTBOUND",
			"LOGS_OUTBOUND_201210",
			"LOGS_OUTBOUND_201211",
			"LOGS_OUTBOUND_201212",
			"LOGS_OUTBOUND_201301",
			"LOGS_OUTBOUND_201302",
			"LOGS_OUTBOUND_201303",
			"LOGS_OUTBOUND_201304",
			"LOGS_OUTBOUND_201305",
			"LOGS_OUTBOUND_201306",
			"LOGS_OUTBOUND_201307",
			"LOGS_OUTBOUND_201308",
			"LOGS_OUTBOUND_201309",
			"LOGS_OUTBOUND_201310",
			"LOGS_OUTBOUND_201311",
			"LOGS_OUTBOUND_201312",
			"LOGS_OUTBOUND_201401",
			"LOGS_OUTBOUND_201402",
			"LOGS_OUTBOUND_201403",
			"LOGS_OUTBOUND_201404",
			"LOGS_OUTBOUND_201405",
			"LOGS_OUTBOUND_201406",
			"LOGS_OUTBOUND_201407",
			"LOGS_OUTBOUND_201408",
			"LOGS_OUTBOUND_201409",
			"LOGS_OUTBOUND_201410",
			"LOGS_OUTBOUND_201411",
			"LOGS_OUTBOUND_201412",
			"LOGS_OUTBOUND_201501",
			"LOGS_OUTBOUND_201502",
			"LOGS_OUTBOUND_201503",
			"LOGS_OUTBOUND_201504",
			"LOGS_OUTBOUND_201505",
			"LOGS_OUTBOUND_201506",
			"LOGS_OUTBOUND_201507",
			"LOGS_OUTBOUND_201508",
			"LOGS_OUTBOUND_201509",
			"LOGS_OUTBOUND_201510",
			"LOGS_OUTBOUND_201511",
			"LOGS_OUTBOUND_201512",
			"MESSAGES",
			"MESSAGES_GROUP",
			"MM_CONV_FACT_INB",
			"MM_GOODSRECEIPT_OUTB",
			"MM_GP_OUTB",
			"MM_LIVCONTRACTOR_DEDUCT",
			"MM_LIVCONTRACTOR_OUTB",
			"MM_MATMAS_INB",
			"MM_PLANT_INB",
			"MM_PLANT_OUTB",
			"MM_POCONTRACTOR_OUTB",
			"MM_PURCHASE_ORDERS",
			"MM_PURCHASE_REQUESTS",
			"MM_RETURNDELIVERY_OUTB",
			"MM_SUPPDEBITNOTESINB",
			"MM_VENDOR_MAS_INB",
			"PS_PROJ_DEF",
			"PS_PROJ_PLAN_BUDGET",
			"PS_WBS_MASTER",
			"SD_BOOK_CONTR_OUTB",
			"SD_BOOK_CONTR_OUTB_JOINOWNER",
			"SD_CHARGECODE",
			"SD_CONTARCT_UTILITIES",
			"SD_CUSTO_REQU_DEDUC_OUTB",
			"SD_CUSTOMER_OUTB1",
			"SYNC_APPS_HOST",
			"SYNC_ATTACH_FILES",
			"SYNC_QUEUE",
			"SYNC_QUEUE_LOGS",
			"SYNC_QUEUE_PARAM",
			"SYNC_QUEUE_PARAM_LOGS",
			"SYNC_SEQUENCE"
			};
	
	public void DropTable(String owner, String table_name) throws Exception {
		Connection conn = new dbHelper().getConn();
		conn.setAutoCommit(true);
		try{
			String sql = " DROP TABLE  "+ owner +"." + table_name;
			
			for(String value : PermananceTable){
				if(value.toUpperCase().equals(CommonService.IsNullRString(table_name).toUpperCase()))
					throw new Exception("ERROR Can't " + sql  + " is Permanance Table !!! DR.TREE");
			}
			
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sql);
			
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			if(!conn.isClosed())
				conn.close();
			
			conn = null;
		}
	}

	/**
	 * 
	 * @param table_temp
	 * @throws Exception
	 */
	public void CreateTableFromExcel(String owner, String table_name, boolean isDropTableFirst) throws Exception {
		File inputWorkbook = new File(inputFile);
		Connection conn = new dbHelper().getConn();
		conn.setAutoCommit(true);
		
		if(CommonService.IsNull(owner))
			owner = this.owner;			
		
		Workbook w;
		try{			
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			
			Sheet sheet = w.getSheet(0);
			// Loop over first 10 column and lines					
				
			if(new TableServices().TableExists(owner, table_name)){
				if(isDropTableFirst)
					DropTable(owner, table_name);
				else
					throw new Exception("Exception :: OWNER="+ owner +", "+ table_name +"=TableExists");
			}				
			
			StringBuffer _strQuery = new StringBuffer();			
			_strQuery.append(" CREATE TABLE "+ owner+"."+table_name +" ( ");
			_strQuery.append(" FIELD_ID         INTEGER ");
			
			int column_int = 0;
			if(sheet.getRows() > 0)
			{
				int i = 0; //Row Hrader
					for (int j = 0; j < sheet.getColumns(); j++) {
						Cell cell = sheet.getCell(j, i);
						String column_header_name = CommonService.IsNullRString(cell.getContents()).replace(" ", "");
						if(CommonService.IsNull(column_header_name)){
							column_int++;
							_strQuery.append(" ,FIELD_"+ Utilitys.leftPad((column_int)+"", 3, "0") +"    VARCHAR2(2000)");
						}else{
							_strQuery.append(" ,"+ column_header_name +"    VARCHAR2(2000) ");
						}
					}
					
					_strQuery.append(", ISACTIVE         VARCHAR2(1) ");
					_strQuery.append(", EXCEL_CREATED_ON   DATE   DEFAULT CURRENT_DATE ");
					_strQuery.append(", EXCEL_UPDATED_ON   DATE   DEFAULT NULL ");
					_strQuery.append(" ) ");
					
					System.out.println(_strQuery.toString() + "\n");
					
					Statement stmt = conn.createStatement();
					stmt.executeUpdate(_strQuery.toString());
			}
						
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			if(!conn.isClosed())
				conn.close();
			
			conn = null;
		}
	}
	
	
	

}
